package larpplanner.database;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class DBHandler {

	private final String HOST_ADRR;
	private final short  PORT;
	private final String SCHEME_NAME;
	private final String USERNAME;
	private final String PASSWORD;
	
	private Connection conn;
	private Lock conn_lock;
	private AtomicInteger conn_user_count;
	
	public DBHandler(String host, short port, String scheme, String userName, String password) {
		
	    HOST_ADRR      = host;
	    PORT           = port;
	    SCHEME_NAME    = scheme;
	    USERNAME       = userName;
	    PASSWORD       = password;
		
		conn_lock = new ReentrantLock();
		conn_user_count = new AtomicInteger(0);
	}
	
	private boolean openConnection() {
			
	        //conn_lock.lock();
	        //int users = conn_user_count.incrementAndGet();
	    
	        //if (users == 1) // need to create connection
	    
	        if (conn != null) // open connection once, and leave it open
	            return true;
	    
    			try { 
    				Class.forName("com.mysql.jdbc.Driver"); // loading the driver
    				conn = DriverManager.getConnection("jdbc:mysql://" + HOST_ADRR
                        + ":" + PORT + "/" + SCHEME_NAME, USERNAME, PASSWORD);
    				conn.setAutoCommit(false);
    			}
    			catch (com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException e) {
    				//if schema does not exist - create it
    				try {
    					//set conn without schema
						conn = DriverManager.getConnection("jdbc:mysql://" + HOST_ADRR
						        + ":" + PORT , USERNAME, PASSWORD);
						//create schema
						String schema = Parser.readFileAsString("sql_files/LARP-schema-script-new.sql");
						schema = "CREATE SCHEMA " + SCHEME_NAME + ";\nUSE " + SCHEME_NAME + ";\n" + schema;
						executeScript(schema);
						//set conn with schema
						conn = DriverManager.getConnection("jdbc:mysql://" + HOST_ADRR
		                        + ":" + PORT + "/" + SCHEME_NAME, USERNAME, PASSWORD);
		    				conn.setAutoCommit(false);
					} catch (SQLException e1) {
						conn = null;
						e1.printStackTrace();
						return false;
					}
    			}
    			catch (Exception e) {
    			    e.printStackTrace();
    			    conn = null;
    			    //conn_user_count.decrementAndGet();
    			    //conn_lock.unlock();
    				return false;
    			}
    			
			//conn_lock.unlock();
			return true;
		}
	
	private void closeConnection() {
	    
	    return; // never close
	    
//	    conn_lock.lock();
//        int users = conn_user_count.decrementAndGet();
//    
//		try {
//		    if (users == 0)
//		        conn.close();
//		}
//		catch (SQLException e) {
//			System.out.println("Unable to close the connection - " + e.toString());
//		}
//		
//		conn_lock.unlock();
	}
	
	public <T> T get(Class<T> cls, long id) {
		
	    return get(cls, "%ID% =" + id);
	}
	
    public <T> T get(Class<T> cls, String whereCondition) {
        List<T> list = getList(cls, true, whereCondition, null);

        return list.isEmpty() ? null : list.get(0);
    }
	
    public <T> List<T> getAll(Class<T> c) {
        return getList(c, null);
    }
    
    public <T> List<T> getList(Class<T> cls, String whereCondition) {
        return getList(cls, true, whereCondition, null);
    }
    
    /**
     * 
     * @param cls
     * @param loadRelations
     * @param whereCondition
     * @param sortByCol the name of the colum to sort by. if it starts with '-' then the it's in reversed order
     * @return
     */
	public <T> List<T> getList(Class<T> cls, boolean loadRelations, String whereCondition, String sortByCol) {
		T object = null;
		Vector<T> returnedList = new Vector<T>();
		Long id = null;

		openConnection();
		try {		    
            String table_name = cls.getAnnotation(DBTable.class).value();
			
            // get primary key field name
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getPrimaryKeys(null, null, table_name);
            String primaryKeyName;
            if (rs.next()) 
	            primaryKeyName = rs.getString("COLUMN_NAME");
            else 
                primaryKeyName = null;
            // TODO: add different selects           
            
			String query = "SELECT * FROM " + table_name;
			
			if (whereCondition != null)
			    query += " WHERE " + whereCondition.replace("%ID%", primaryKeyName);

			if (sortByCol != null) {
				if(sortByCol.charAt(0)=='-')
					query += " ORDER BY " + sortByCol.substring(1) + " DESC ";
				else query += " ORDER BY " + sortByCol;
			}

			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			
			
			while (rs.next()) {
			    object = cls.newInstance();
			    returnedList.add(object);
    		
    			List<Field> fields = getAllFields(cls);
    			if(primaryKeyName!=null) id = rs.getLong(primaryKeyName);
    			
    			for (Field f : fields) {
    			    
    			    if (f.isAnnotationPresent(DBCol.class)) {   // Simple column
    			        String col_name = f.getAnnotation(DBCol.class).value();
    			        f.set(object, rs.getObject(col_name));
    			    }
    			    if (loadRelations && f.isAnnotationPresent (DBListRelation.class)) {
    			        DBListRelation relation = f.getAnnotation(DBListRelation.class);
    			        ParameterizedType type = (ParameterizedType) f.getGenericType();
    			        Class<?> listType = type.getActualTypeArguments()[0].getClass();
    			        List<Object> objList = readRelatedList(relation, listType, id);
    			        f.set(object, objList);
    			    }
    			    if (loadRelations && f.isAnnotationPresent (DBMapRelation.class)) {
                        DBMapRelation relation = f.getAnnotation(DBMapRelation.class);
                        ParameterizedType type = (ParameterizedType) f.getGenericType();
                        Class<?> a_type = (Class<?>) type.getActualTypeArguments()[0];
                        Class<?> b_type = (Class<?>) type.getActualTypeArguments()[1];
                        Map<Object, Object> objList = readRelatedMap(relation, a_type, b_type, primaryKeyName, id);
                        f.set(object, objList);
                    }
                    
    			}
    			
			}
			// else object will remain null;
			    
			rs.close();
			stmt.close();
		}
		catch (Exception e) {
		    e.printStackTrace();
		}
		closeConnection();
		
		return returnedList;
	}
	
	/**
	 * @return a list of all the fields of the object, including its inherited fields.
	 */
	static List<Field> getAllFields(Class<?> type) {

	    List<Field> fields = new ArrayList<Field>();
		List<String> fieldsName = new ArrayList<String>();

	    for (Field field: type.getDeclaredFields()) {
	        fields.add(field);
	        fieldsName.add(field.getName());
	    }
	    
		if (type.getSuperclass() != null)
	        fields.addAll( getAllFieldsStep(fieldsName, type.getSuperclass()) );

	    return fields;
	}
	
	/**
	 * A subfunction used for getAllFields function
	 */
	private static List<Field> getAllFieldsStep(List<String> fieldsName, Class<?> type) {
		List<Field> fields = new ArrayList<Field>();
		for (Field field: type.getDeclaredFields()) {
	        if(fieldsName.contains(field.getName())) continue;
			fields.add(field);
	        fieldsName.add(field.getName());
	    }
	    if (type.getSuperclass() != null) {
	        fields.addAll( getAllFieldsStep(fieldsName, type.getSuperclass()) );
	    }

	    return fields;
	}

	
	/**
     * @pre the object has a single numeric primary key
     * @return the generated id for the object, or -1 if error
     */
    public <T> long put(Class<T> cls, T object) {
    	long id = -1;
    	boolean hasOnePrimaryKey = false;
    	boolean hasDBListRelation = false;

    	openConnection();
        try {
            String table_name = cls.getAnnotation(DBTable.class).value();
            Statement stmt = conn.createStatement();
            
            // get primary key field name
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getPrimaryKeys(null, null, table_name);
            rs.next();
            String primaryKeyName = rs.getString("COLUMN_NAME");
            if(!rs.next()) 
            	hasOnePrimaryKey = true; //the primary key is more than one field
            else primaryKeyName = " ";
            
            List<String> cols = new LinkedList<String>(), vals = new LinkedList<String>();
            
            Field[] fields = cls.getDeclaredFields();
            
            for (Field f : fields) {
                
                if (f.isAnnotationPresent(DBCol.class)) {   // Simple column
                    String col_name = f.getAnnotation(DBCol.class).value();
                    
                    if(col_name.equals(primaryKeyName))
                        continue; // Skip over primary key, it is automatically determined by DB engine
                    
                    cols.add(col_name);
                    if(f.get(object) != null)
                    	vals.add("'" + f.get(object).toString() + "'");
                    else
                    	vals.add("NULL");
                }
                if (f.isAnnotationPresent(DBListRelation.class)) {               	
//                    DBRelation relation = f.getAnnotation(DBRelation.class);
//                    ParameterizedType type = (ParameterizedType) f.getGenericType();
//                    Class<?> listType = type.getActualTypeArguments()[0].getClass();
//                    List<Object> objList = writeRelatedList(relation, listType, primaryKeyName, id);
                }
                if (f.isAnnotationPresent(DBMapRelation.class)) {
                    // TODO: 
                }
            }
            
            String insert = "INSERT INTO " + table_name + "(" + prettyList(cols, ",") + ") VALUES (" + prettyList(vals, ",") + ");";
            
            if(hasOnePrimaryKey) {
	            stmt.executeUpdate(insert,new String[]{"ID"});
	            rs = stmt.getGeneratedKeys();
	            rs.next();
	            id = rs.getInt(1);
	            
	          //now that we have the generated key, insert the object's relations
	            if(hasOnePrimaryKey) {
		            for (Field f : fields) {
		            	//update the id in the object
		            	if (f.isAnnotationPresent(DBCol.class)) {               
		                    if(f.getAnnotation(DBCol.class).value().equals(primaryKeyName))
		                        f.setLong(object,id);
		                }
		            	//insert in relation tables
		            	if (f.isAnnotationPresent(DBListRelation.class)) {
		                	hasDBListRelation = true;
		                	DBListRelation relation = f.getAnnotation(DBListRelation.class);
		                	List<Object> lst = (List<Object>) f.get(object);
		                	if(lst!=null)
			                	for(Object obj : lst) {
			                		stmt.addBatch("INSERT INTO " + relation.table() + "(" + relation.join_col() + "," + relation.val_col() + ") VALUES (" + id + ",'" + obj + "');");
			                	}
		                }
		            }
		            stmt.executeBatch();
	            }
	            
            }
            else {
            	stmt.executeUpdate(insert);
            	id = 0;
            }

            conn.commit();
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        closeConnection();

        return id;
    }
	
    public boolean deleteAll() {
    	String scheme = Parser.readFileAsString("sql_files/LARP-schema-script-new.sql");
        scheme = "DROP SCHEMA IF EXISTS " + SCHEME_NAME + ";" + "\nCREATE SCHEMA " + SCHEME_NAME + ";" + "\nUSE " + SCHEME_NAME + ";" + "\n" + scheme;
    	return executeScript(scheme);
    }
    
    public <T> boolean del(Class<T> cls, long id) {
		 return del(cls, "%ID% =" + id);
	}

	public <T> boolean del(Class<T> cls, String whereCondition) {
		boolean bol;
	    bol = openConnection();
	    try {
            String table_name = cls.getAnnotation(DBTable.class).value();
            
            // get primary key field name
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getPrimaryKeys(null, null, table_name);
            rs.next();
            
            String primaryKeyName = rs.getString("COLUMN_NAME");
            String query = "DELETE FROM " + table_name;
            if (whereCondition != null)
            	query += " WHERE " + whereCondition.replace("%ID%", primaryKeyName);
            rs.close();

            Statement stmt = conn.createStatement();
            stmt.execute(query);
            
            conn.commit();
            stmt.close();
        
        }
        catch (Exception e) {
            e.printStackTrace();
            bol = false;
        }
        closeConnection();
        return bol;
	}
	
	/**
     * @pre the object has a single numeric primary key
     * @return the generated id for the object, or -1 if error
     */
    public <T> long update(Class<T> cls, T object) {
    	long id = -1;

    	openConnection();
        try {
            String table_name = cls.getAnnotation(DBTable.class).value();
            
            // get primary key field name
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getPrimaryKeys(null, null, table_name);
            rs.next();
            String primaryKeyName = rs.getString("COLUMN_NAME");
            
            List<String> sets = new LinkedList<String>();
            
            Field[] fields = cls.getDeclaredFields();
            
            for (Field f : fields) {
                
                if (f.isAnnotationPresent(DBCol.class)) {   // Simple column
                    String col_name = f.getAnnotation(DBCol.class).value();
                    
                    if(col_name.equals(primaryKeyName)) {
                    	id = f.getLong(object);
                        continue; //don't set the primary key
                    }
                    
                    String set = col_name + "=";
                    if(f.get(object) != null)
                    	set += ("'" + f.get(object).toString() + "'");
                    else
                    	set += "NULL";
                    sets.add(set);
                }
                if (f.isAnnotationPresent(DBListRelation.class)) {
                    // TODO:
//                    DBRelation relation = f.getAnnotation(DBRelation.class);
//                    ParameterizedType type = (ParameterizedType) f.getGenericType();
//                    Class<?> listType = type.getActualTypeArguments()[0].getClass();
//                    List<Object> objList = writeRelatedList(relation, listType, primaryKeyName, id);
                }
                if (f.isAnnotationPresent(DBMapRelation.class)) {
                    // no need to update map relations
                }
            }
            
            String update = "UPDATE " + table_name + " SET " + prettyList(sets, ",") + " WHERE " + primaryKeyName + "=" + id + ";";
            
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(update);
            
            conn.commit();
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        closeConnection();

        return id;
    }
        
	/**
	 * @param relation 
	 * @pre the from TYPE must not contain lists
	 * @return a list of objects that suit the query:  " SELECT * FROM from WHERE where = id "
	 */
	private List<Object> readRelatedList(DBListRelation relation, Class<?> listType, long id) {

		List<Object> list = new ArrayList<Object>();
		
		openConnection();
		try {
            String query = "SELECT * FROM " + relation.table() + " WHERE " + relation.join_col() + "=" + id;
			// System.out.println(query);

            Statement stmt = conn.createStatement();	
            ResultSet rs = stmt.executeQuery(query);
            
			while (rs.next()) {
				Object obj = rs.getObject(relation.val_col());
				list.add(obj);
			}

			rs.close();
			stmt.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		closeConnection();
		
		return list;
	}
	
	private Map<Object, Object> readRelatedMap(DBMapRelation relation, Class<?> aType, Class<?> bType, String where, long id) {

	    Map<Object, Object> map = new HashMap<Object, Object>();
        
	    openConnection();
        try {
            String query = "SELECT * FROM " + relation.table() + " WHERE " + where + "=" + id;

            Statement stmt = conn.createStatement();    
            ResultSet rs = stmt.executeQuery(query);
            
            while (rs.next()) {
                Object a_id = rs.getObject(relation.cols()[0]);
                Object b_id = rs.getObject(relation.cols()[1]);
                map.put(get(aType, (Long) a_id), get(bType, (Long)b_id));
            }

            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        closeConnection();
        
        return map;
    }
	
	/**
	 * @return true if the schema contains a character from freebase
	 */
	public boolean hasFreebaseChar() {
		Long res = (long)1;
		openConnection();
		try {		    
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT count(*)>0 AS res FROM fic_character WHERE " + FicCharacter.FREEBASEID_COL + " != ''");
			
			
			while (rs.next()) {
    			res = rs.getLong("res");
			}
			rs.close();
			stmt.close();
		}
		catch (Exception e) {
		    e.printStackTrace();
		}
		closeConnection();
		return res==1;
	}

	public static String prettyList(List<?> list, String delim) {
		if(list.isEmpty()) return "";
	    StringBuilder pretty = new StringBuilder();
	    
	    for (Object obj : list)
            pretty.append(obj + ",");
	    
	    pretty.deleteCharAt(pretty.length()-1);
	    
	    return pretty.toString();
	}
	
	public boolean executeScript(String sql) {
		Statement stmt;
		boolean bool = true;

		openConnection();
		try {
			stmt = conn.createStatement();	
			
			String[] commands = sql.split(";");
			for(String cmd : commands) stmt.addBatch(cmd);
			stmt.executeBatch();
			
		} catch (SQLException e) {
			e.printStackTrace();
			bool = false;
		}
		closeConnection();
		
		return bool;
	}

}
